﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XLB.ExtensionMethodCore;

namespace XLB.ExtensionMethod.DB
{
    public class SQLDapperHelper
    {
        public static string connectionString = DotNetCoreReadSettingManager.GetAppSettingsValue("DBConnectionStrings:MySql");

        static IDbTransaction trans;

        static IDbConnection iDbConnection;
        /// <summary>
        /// 执行查询语句，返回List
        /// </summary>
        /// <typeparam name="T">返回list的类型</typeparam>
        /// <param name="strSQL">sql语句</param>
        /// <returns></returns>
        public static List<T> Query<T>(string strSQL)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Query<T>(strSQL).ToList();
            }
        }

        /// <summary>
        /// 执行查询语句，返回List
        /// </summary>
        /// <typeparam name="T">返回list的类型</typeparam>
        /// <param name="strSQL">sql语句</param>
        /// <returns></returns>
        public static List<T> Query<T>(string strSQL, Dictionary<string, object> dictionary, SqlConnection conn, SqlTransaction transaction)
        {
            IDbConnection connection = conn;
            connection.BeginTransaction();
            return connection.Query<T>(strSQL).ToList();
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public static void OpenConnection()
        {
            try
            {
                if (iDbConnection != null)
                {
                    iDbConnection.Close();
                    iDbConnection = null;
                }
                iDbConnection = new SqlConnection(connectionString);
                iDbConnection.Open();
            }
            catch (SqlException e)
            {
                iDbConnection.Close();
                throw e;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public static void CloseConnection()
        {
            try
            {
                if (iDbConnection != null)
                {
                    iDbConnection.Close();
                    iDbConnection = null;
                }
            }
            catch (SqlException e)
            {
                iDbConnection.Close();
                throw e;
            }
        }

        /// <summary>
        /// 开启事务
        /// </summary>
        /// <param name="conn"></param>
        public static void BeginTransaction(SqlConnection conn)
        {
            IDbConnection connection = conn;

            trans = connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="conn"></param>
        public static void Commit(SqlConnection conn)
        {
            IDbConnection connection = conn;
            trans = connection.BeginTransaction();
            trans.Commit();  //提交事务
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="conn"></param>
        public static void Rollback(SqlConnection conn)
        {
            IDbConnection connection = conn;
            trans = connection.BeginTransaction();

            trans.Rollback();//回滚事务
        }

        /// <summary>
        /// 执行查询语句，返回List
        /// </summary>
        /// <typeparam name="T">返回list的类型</typeparam>
        /// <param name="strSQL">sql语句</param>
        /// <param name="dictionary">参数字典</param>
        /// <returns></returns>
        public static List<T> Query<T>(string strSQL, Dictionary<string, object> dictionary)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Query<T>(strSQL, new DynamicParameters(dictionary)).ToList();
            }
        }

        public static T GetSingle<T>(string strSQL, Dictionary<string, object> dictionary = null)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                List<T> list = connection.Query<T>(strSQL, new DynamicParameters(dictionary)).ToList();
                return list != null && list.Count > 0 ? list[0] : default;
            }
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <typeparam name="T">数据类型 content可以是一个对象也可以是一个集合</typeparam>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int ExecuteNonQuery<T>(string sql, T obj)
        {
            int result = 0;
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    result = conn.Execute(sql, obj);
                }
            }
            catch { }
            return result;
        }

        /// <summary>
        /// 取得数据库连接
        /// </summary>
        /// <param name="DBKey">数据库连接主键</param>
        /// <returns></returns>
        public static SqlConnection GetConnByKey(string connectionStringKey)
        {
            ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[connectionStringKey];
            string constr = css.ConnectionString;
            SqlConnection con = new SqlConnection(constr);
            return con;
        }

        /// <summary>
        /// 执行查询类型的存储过程(无返回参数)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="dictionary">参数</param>
        /// <param name="buffered">是否缓存</param>
        /// <param name="commandTimeout">设置超时时间（毫秒）</param>
        /// <param name="commandType">指定的sql语句为存储过程的类型</param>
        /// <returns></returns>
        public static List<T> ExecStoredProcedureQuery<T>(string storedProcedureName, Dictionary<string, object> dictionary = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null)
        {
            IDbConnection connection;
            var list = new List<T>();
            try
            {
                if (iDbConnection != null)
                {
                    connection = iDbConnection;
                }
                else
                {
                    connection = new SqlConnection(connectionString);
                }
                list = connection.Query<T>(storedProcedureName  //存储过程参数
                    , dictionary  //存储过程参数
                    , null        //事务对象
                    , buffered    //是否缓存
                    , commandTimeout
                    , commandType
                    ).ToList();
                return list;
            }
            catch (SqlException e)
            {
                //connection.Close();
                throw e;
            }
        }

        /*
        /// <summary>
        /// 执行存储过程(有返回参数)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="dictionary">参数</param>
        /// <param name="buffered">是否缓存</param>
        /// <param name="commandTimeout">设置超时时间（毫秒）</param>
        /// <param name="commandType">指定的sql语句为存储过程的类型</param>
        /// <returns></returns>
        public static List<T> ExecStoredProcedure<T>(string storedProcedureName, Dictionary<string, object> dictionary = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null)
        {
            IDbConnection connection;
            int count;
            try
            {
                if (iDbConnection != null)
                {
                    connection = iDbConnection;
                }
                else
                {
                    connection = new SqlConnection(connectionString);
                }
                count = connection.ExecuteAsync(storedProcedureName  //存储过程参数
                    , dictionary  //存储过程参数
                    , null        //事务对象
                    , buffered    //是否缓存
                    , commandTimeout
                    , commandType
                    );
                return list;
            }
            catch (SqlException e)
            {
                //connection.Close();
                throw e;
            }
        }
        */
    }
}
